# This file is used to create the user_info_ext table in the database.

'''

-- 通知类型

-- 通知类型
CREATE TABLE IF NOT EXISTS notification_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    current_status VARCHAR(50) DEFAULT 'active' -- active, blocked
);

-- 插入第一个模板信息，如果 id 已经存在则不执行插入
INSERT INTO notification_types (id, template_code, type_name)
VALUES (1, 'SMS_467585524', '原水桶缺水')
ON CONFLICT (id) DO NOTHING;

-- 插入第二个模板信息，如果 id 已经存在则不执行插入
INSERT INTO notification_types (id, template_code, type_name)
VALUES (2, 'SMS_467430469', '废水箱已满')
ON CONFLICT (id) DO NOTHING;

-- 插入第三个模板信息，如果 id 已经存在则不执行插入
INSERT INTO notification_types (id, template_code, type_name)
VALUES (3, 'SMS_467385534', '最大泡次提醒')
ON CONFLICT (id) DO NOTHING;

-- 插入第四个模板信息，如果 id 已经存在则不执行插入
INSERT INTO notification_types (id, template_code, type_name)
VALUES (4, 'SMS_467500556', '滤芯快到期')
ON CONFLICT (id) DO NOTHING;

'''
import logging
import datetime
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class NotificationTypesTableMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_notification_type(self, template_code, type_name):
        try:
            sql = "INSERT INTO notification_types (template_code, type_name) VALUES (?,?)"

            self.cursor.execute(sql, (template_code, type_name))
            
        except Exception as e:
            logger.error("Insert notification type failed. Error: {}".format(e))

        else:

            self.conn.commit()
            logger.debug("Insert notification type successfully.")

    def update_notification_type_current_status_by_id(self, id, current_status="blocked"):

        sql = "UPDATE notification_types SET current_status =? WHERE id = ?"

        self.cursor.execute(sql, (current_status, id))

        self.conn.commit()

        logger.debug("Update current status successfully.")

    def update_notification_type_by_id(self, id, template_code, type_name):

        sql = "UPDATE notification_types SET template_code =?, type_name =? WHERE id = ?"

        self.cursor.execute(sql, (template_code, type_name, id))

        self.conn.commit()

        logger.debug("Update notification type successfully.")

    def get_notification_types(self):

        sql = "SELECT * FROM notification_types"

        self.cursor.execute(sql)

        results = self.cursor.fetchall()

        for result in results:

            # 将 created_at 字段转换为北京时间 '%Y-%m-%d %H:%M:%S
            utc_time = datetime.datetime.strptime(result["created_at"], '%Y-%m-%d %H:%M:%S')
            beijing_time = utc_time + datetime.timedelta(hours=8)
            result["created_at"] = beijing_time.strftime('%Y-%m-%d %H:%M:%S')

        return results
    
    def get_notification_types_by_current_status_active(self):

        sql = "SELECT * FROM notification_types where current_status = 'active'"

        self.cursor.execute(sql)

        results = self.cursor.fetchall()

        for result in results:

            # 将 created_at 字段转换为北京时间 '%Y-%m-%d %H:%M:%S
            utc_time = datetime.datetime.strptime(result["created_at"], '%Y-%m-%d %H:%M:%S')
            beijing_time = utc_time + datetime.timedelta(hours=8)
            result["created_at"] = beijing_time.strftime('%Y-%m-%d %H:%M:%S')

        return results